package me.passtime.projt.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: Lee
 * Date: 11-12-7
 * Time: 下午5:06
 */
public class DB {
    private static final String TAG = "DBAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    private Context context;

    private DB(Context context) {
        this.context = context;
    }

    private static DB dB = null;

    public static DB getInstance(Context context) {
        if (null == dB) dB = new DB(context);
        return dB;
    }

    public DB open() throws SQLException {
        mDbHelper = new DatabaseHelper(this.context);
        mDb = mDbHelper.getWritableDatabase();

        return this;
    }

    public DB close() throws SQLException {
        mDb.close();
        mDbHelper.close();

        return this;
    }

    public void clearDBTableByName(String tableName) {
        mDb.delete(tableName, "1=1", null);
    }


    /**
     * Account operate
     */
    private List<Account> queryAccount(String whereCase) {
        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
        builder.setTables(DBConfig.Account.TABLE_NAME);
        builder.appendWhere(whereCase);
        String arrColumn[] = {
                DBConfig.Account.ID, DBConfig.Account.PHONE_NUMBER, DBConfig.Account.PASSWORD, DBConfig.Account.REMEMBER
        };
        Cursor c = builder.query(mDb, arrColumn, null, null, null, null, null);
        c.moveToFirst();
        List<Account> data = new ArrayList<Account>();
        while (!c.isAfterLast()) {
            Account item = new Account();
            item.id = c.getInt(c.getColumnIndex(arrColumn[0]));
            item.phoneNumber = c.getString(c.getColumnIndex(arrColumn[1]));
            item.password = c.getString(c.getColumnIndex(arrColumn[2]));
            item.remember = c.getString(c.getColumnIndex(arrColumn[3]));
            data.add(item);
            c.moveToNext();
        }
        c.close();
        return data;
    }

    public long insertAccount(Account account) {
        ContentValues values = new ContentValues();
        values.put(DBConfig.Account.PHONE_NUMBER, account.phoneNumber);
        values.put(DBConfig.Account.PASSWORD, account.password);
        values.put(DBConfig.Account.REMEMBER, account.remember);
        return mDb.insertOrThrow(DBConfig.Account.TABLE_NAME, null, values);

    }

    public Account queryRememberAccount() {
        List<Account> accounts = new ArrayList<Account>();
        StringBuilder sb = new StringBuilder();
        sb.append(DBConfig.Account.REMEMBER + "='T'");
        accounts = this.queryAccount(sb.toString());
        if (accounts.size() > 0) {
            return accounts.get(0);
        }
        return null;
    }

    public Account queryAccountByPhoneNumber(String phoneNumber) {
        List<Account> accounts = new ArrayList<Account>();
        StringBuilder sb = new StringBuilder();
        sb.append(DBConfig.Account.PHONE_NUMBER + "='" + phoneNumber + "'");
        accounts = this.queryAccount(sb.toString());
        if (accounts.size() > 0) {
            return accounts.get(0);
        }
        return null;
    }

    public void rememberAccount(String phoneNumber, String password) {
        updateAllAccountToNotRemember();
        ContentValues values = new ContentValues();
        values.put(DBConfig.Account.REMEMBER, "T");
        values.put(DBConfig.Account.PASSWORD, password);
        StringBuilder sb = new StringBuilder();
        sb.append(DBConfig.Account.PHONE_NUMBER + "='" + phoneNumber + "'");
        mDb.update(DBConfig.Account.TABLE_NAME, values, sb.toString(), null);
    }

    public void clearRememberAccount(String phoneNumber) {
        updateAllAccountToNotRemember();
        ContentValues values = new ContentValues();
        values.put(DBConfig.Account.REMEMBER, "F");
        StringBuilder sb = new StringBuilder();
        sb.append(DBConfig.Account.PHONE_NUMBER + "='" + phoneNumber + "'");
        mDb.update(DBConfig.Account.TABLE_NAME, values, sb.toString(), null);
    }

    private void updateAllAccountToNotRemember() {
        ContentValues values = new ContentValues();
        values.put(DBConfig.Account.REMEMBER, "F");
        StringBuilder sb = new StringBuilder();
        sb.append(DBConfig.Account.REMEMBER + "='T'");
        mDb.update(DBConfig.Account.TABLE_NAME, values, sb.toString(), null);
    }

    public void updatePassword(String phoneNumber, String password) {
        Account account = this.queryAccountByPhoneNumber(phoneNumber);
        if (account != null) {
            ContentValues values = new ContentValues();
            values.put(DBConfig.Account.PASSWORD, password);
            StringBuilder sb = new StringBuilder();
            sb.append(DBConfig.Account.PHONE_NUMBER + "='" + phoneNumber + "'");
            mDb.update(DBConfig.Account.TABLE_NAME, values, sb.toString(), null);
        }
    }


    private static class DatabaseHelper extends SQLiteOpenHelper {

        public DatabaseHelper(Context context) {
            super(context, DBConfig.DB_NAME, null, DBConfig.DB_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DBConfig.Account.CREATE_ACCOUNT);
            db.execSQL(DBConfig.PaymentRecord.CREATE_PAYMENTRECORD);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + DBConfig.Account.TABLE_NAME);
            db.execSQL("DROP TABLE IF EXISTS " + DBConfig.PaymentRecord.TABLE_NAME);
            onCreate(db);
        }

    }
}
